Unsupported Hive type: jsonに負けず、Amazon Athenaでjson形式データのテーブルを作る

データアナリティクス事業本部インテグレーション部コンサルティングチーム・新納（にいの）です。

json形式のデータをテーブル化しようとしたら躓き、調べているうちにAmazon Athenaへの理解が深まったのでシェアします。

やりたいこと

Athenaに以下のようなjson形式のデータが入っています。

{"name": "Susan Smith","org": "engineering","projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}

データは以下のドキュメントページを元にして作成しています。

JSON からのデータの抽出 - Amazon Athena

このデータを以下のような形に構造化したテーブルを作成したいです。

name	org	p_name	completed
"Susan Smith"	"engineering"	"project1"	FALSE
"Susan Smith"	"engineering"	"project2"	TRUE

Unsupported Hive type: jsonが発生

以下のCREATE TABLE文を実行してみます。

CREATE TABLE "default"."json_test_table" WITH (
  format = 'PARQUET',
  external_location = 's3://<バケット名>'
) AS
SELECT a.name,
  a.org,
  json_extract(t.projects, '$.name') as p_name,
  json_extract(t.projects, '$.completed') as completed
FROM (
    select json_extract(blob, '$.name') as name,
      json_extract(blob, '$.org') as org,
      cast(json_extract(blob, '$.projects') as array(json)) as projects
    from "default"."json_test_view"
  ) a
  CROSS JOIN UNNEST(projects) as t(projects)

すると、NOT_SUPPORTED: Unsupported Hive type: json. エラーが発生します。

なお、CREATE TABLEをSELECTにしてみるとエラーは発生せず、期待した結果が取得されます。

SELECT
    a.name,
    a.org,
    json_extract(t.projects, '$.name') as p_name,
    json_extract(t.projects, '$.completed') as completed
FROM (
    SELECT 
        json_extract(blob, '$.name') as name,
        json_extract(blob, '$.org') as org,
        cast(json_extract(blob, '$.projects') as array(json)) as projects
    FROM
        "default"."json_test_view"
) a  cross join
unnest(projects) as t(projects)

解決策

データ型がjsonになっている項目に対し、CASTで適切なデータ型にします。

CREATE TABLE "default"."json_test_table" WITH (
  format = 'PARQUET',
  external_location = 's3://<バケット名>'
) AS
select a.name,
  a.org,
  cast(json_extract(t.projects, '$.name') as varchar) as p_name,
  cast(json_extract(t.projects, '$.completed') as boolean) as completed
from (
    select cast(json_extract(blob, '$.name') as varchar) as name,
      cast(json_extract(blob, '$.org') as varchar) as org,
      cast(json_extract(blob, '$.projects') as array(json)) as projects
    from "default"."json_test_view"
  ) a
  cross join unnest(projects) as t(projects)

解説

Amazon Athenaでは、CREATE TABLEなどのDDL（データ定義言語）はHiveが使われ、それ以外のステートメントではインメモリクエリエンジンであるPrestoが使われています。

Q: Athena で Hive クエリを実行できますか? Amazon Athena では、Hive を DDL (データ定義言語) 用や、テーブルおよび/またはパーティションの作成、変更、削除にのみ使用します。

（中略）

Amazon S3 で SQL クエリを実行する場合、Athena では Presto が使用されます。Amazon S3 のデータをクエリする場合、ANSI 準拠 SQL SELECT ステートメントを実行できます。

よくある質問 - Amazon Athena | AWS

そして、Hiveの場合サポートしているデータ型にjsonはありません。このため、SELECTは問題なく実行できてもCREATE TABLEはエラーになってしまいました。上述の通り、DDLでもサポートしているデータ型に変換することでテーブル作成できるようになります。

• Amazon Athena のデータ型 - Amazon Athena

「どうしてAthenaのクエリエンジンはPrestoなのにDDL機能だけHiveなの？」と疑問に思われた方は以下のエントリも合わせてご参照ください。

• スキーマオンリードを理解してAmazon Athenaのアーキテクチャに詳しくなる | DevelopersIO

最後に

DDL文実行でNOT_SUPPORTED: Unsupported Hive type: json. が発生したのでjson型のカラムを適切なデータ型にCASTしてエラー解消したというお話でした。このようなデータ型関連のエラーが発生した際はtypeof関数を利用してどんなデータ型で結果が返ってきているのか確かめると原因が特定しやすくなるかと思います。

あわせて読みたい